package write;
import java.io.FileWriter;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import CalculateWeights.CalculateAttributeWeights;
import constants.Constants;
public class WriteToCSV
{
   //private Set<String> nameHashSet = new HashSet<String>();
	
	public static void main(String [] args)
   {
	   generateCsvFile("C:\\Aparna\\Output_New\\ResultsDec6\\testOverallWeightFred.csv","C:\\Aparna\\Output_New\\ResultsDec6\\DataForOverallWeightFred.csv"); 
   }
 
   private static void generateCsvFile(String sFileName, String data)
   {
	try
	{
	    FileWriter writer = new FileWriter(sFileName);
	    FileWriter writerData = new FileWriter(data);
	    String nameFlag = new String();
	    String streetAddressFlag = new String();
	    String cityFlag = new String();
	    String stateFlag = new String();
	    String zipcodeFlag = new String();
	    String phoneFlag = new String();
	    String genderFlag = new String();
	    String educationFlag = new String();
	    String occupationFlag = new String();
	    String emailFlag = new String();
	    String ageFlag = new String();
	    String birthdayFlag = new String();
	    String piiFlag = new String();
	    CalculateAttributeWeights calculateWeights = new CalculateAttributeWeights();
	    
	      Connection conn = null;
        try {
//        	String userName = "root";
//            String password = "admin";
//            String url = "jdbc:mysql://localhost/aparna";
//            Class.forName ("com.mysql.jdbc.Driver").newInstance ();
//            conn = DriverManager.getConnection (url, userName, password);
            conn = getConn();
        	System.out.println ("Database connection established");
	    
            BigDecimal finalWeight = new BigDecimal(0.0);
            int weight =0;
            Double nameWeight = new Double(-1.0);
            Double stateWeight = new Double(0.0);
            Double cityWeight = new Double(0.0);
            Double streetWeight = new Double(0.0);
            Double zipWeight = new Double(0.0);
            Double overallLocationWeight = new Double(-1.0);
            Double phoneWeight = new Double(-1.0);
            Double ageWeight = new Double(-1.0);
            Double overallWeight = new Double(0.0);
            String queryName = "Fred";
            
             
            String query1 = "select * from person where name like \'%" + queryName + "%\' and assigned_id <> -1";
            PreparedStatement st1 = conn.prepareStatement(query1);
            ResultSet  rs1 = st1.executeQuery();
            int count = getMinimumIdForName(queryName); 
           
            
           writer.append("Source,Target,Type,ID,Label,Weight");
           writer.append("\r\n");
           writerData.append("Idperson1,Idperson2,Name1,Name2,Name Weight, State1,State2,State Weight,City1,City2,City Weight,Street1,Street2,Street Weight,ZIP1,ZIP2,ZIP Weight, Location Weight, Phone1,Phone2,Phone Weight, Age1,Age2,Age Weight,Overall Weight,URL1,URL2");
           writerData.append("\r\n");
 		   
 	       while(rs1.next())
 	       {
 	    	 String query2 = "select * from person where idperson>? and name like \'%" + queryName + "%\' and assigned_id <> -1;";
             PreparedStatement st2 = conn.prepareStatement(query2);
             st2.setInt(1, count);
           
             ResultSet  rs2 = st2.executeQuery();
                 	  
 	    	  while(rs2.next())// && !(rs1.getString("idperson").equals(rs2.getString("idperson"))))
 	    	   {
 	    		
 	    if(!(rs1.getString("idperson").equals(rs2.getString("idperson"))))
 	    	
 	    {
 	    		  weight = 0;
 	    		
 	    	nameFlag = new String();
 	   	    streetAddressFlag = new String();
 	   	    cityFlag = new String();
 	   	    stateFlag = new String();
 	   	    zipcodeFlag = new String();
 	   	    phoneFlag = new String();
 	   	    genderFlag = new String();
 	   	    educationFlag = new String();
 	   	    occupationFlag = new String();
 	   	    emailFlag = new String();
 	   	    ageFlag = new String();
 	   	    birthdayFlag = new String();
 	   	    piiFlag = new String();
 	    		 
// 	    	
// 	    if(!(rs1.getString("name").equals(null) && !(rs2.getString("name").equals(null))))
// 	    {
 	    	    String name1 = rs1.getString("name") ;  	
 	    		String name2 = rs2.getString("name");
    			nameWeight = calculateWeights.compareNameAttributes(name1, name2);
    			nameFlag = "Y";
				System.out.println("Weight after name is  " +nameWeight);
    	//}
    		
     	
 	      (rs1.getString("state")).replaceAll(" ",""); 
 	  	  (rs2.getString("state")).replaceAll(" ",""); 
 	 	    
 	  	  String state1 = (rs1.getString("state")).replaceAll(" ", "");

 				String state2 = (rs2.getString("state")).replaceAll(" ", "");
	
 				stateWeight = calculateWeights.calculateLocationAttribute(state1, state2);
 	    		
		 		  (rs1.getString("city")).replaceAll(" ",""); 
		   	 	  (rs2.getString("city")).replaceAll(" ",""); 
		   		
	   			String city1 = (rs1.getString("city")).replaceAll(" ", "");
		   		String city2 = (rs2.getString("city")).replaceAll(" ", "");
						cityWeight = calculateWeights.calculateLocationAttribute(city1, city2);
		    			cityFlag = "Y";
						System.out.println("Weight after city is  " +cityWeight);
				 		  			String zipcode1 = (rs1.getString("zipcode").replaceAll(" ", "")); 
                    String zipcode2 = (rs2.getString("zipcode").replaceAll(" ", ""));
 		  				zipWeight = calculateWeights.calculateLocationAttribute(zipcode1, zipcode2);
 		  				zipcodeFlag="Y";
 		  				System.out.println("Weight after zipcode is  " +zipWeight);
 		  				
 		  			rs1.getString("street_address").trim();
  			        rs2.getString("street_address").trim();
  			
	  			String address1Copy = new String (rs1.getString("street_address"));
	  			address1Copy.replaceAll(" ", "");
	  		 	String address2Copy;
	  		    address2Copy = new String (rs2.getString("street_address"));
	  		    address2Copy.replaceAll(" ", "");
  
 	 		   streetWeight = calculateWeights.calculateLocationAttribute(address1Copy,address2Copy);
 			   streetAddressFlag = "Y";
 			   System.out.println("Weight after street_address is  " +streetWeight);
 			   
 			  //calculate Overall Location Weight
 	 		   
 	 		  overallLocationWeight = calculateWeights.calculateLocationWeight(stateWeight, cityWeight, streetWeight, zipWeight);
 			     
   	 
 	 	   	if(!(rs1.getString("phone").equals(null) && !(rs2.getString("phone").equals(null))))
 		    	{
 		    		 	phoneWeight = calculateWeights.comparePhoneAttributes(rs1.getString("phone"), (rs2.getString("phone"))); 
 	 	    			phoneFlag="Y";
 		    			System.out.println("Weight after phone is  " +phoneWeight); 
 		    		  	 	   	
 
	    	   		
	    		if((!(rs1.getString("age").equals(null)) && !(rs2.getString("age").equals(null))))
    		   {
	    			ageWeight = calculateWeights.compareAgeAttributes(rs1.getString("age"),rs2.getString("age"));
    			 // weight = weight + Constants.AGE_WEIGHT;
    			  ageFlag="Y";
    			  System.out.println("Weight after age is  " +ageWeight);
    			  System.out.println("Age weight increased for " + rs1.getString("idperson") + " - " + rs2.getString("idperson"));
    		   }
 	 
 	  }
 	      	overallWeight = nameWeight + overallLocationWeight + phoneWeight + ageWeight;
 	 	   	
 	    		 if(overallWeight<=Constants.THRESHOLD)
 	    		 {
 	    					 
 	    			finalWeight = new BigDecimal(Constants.THRESHOLD/20.0);
 	    			
 	    			System.out.println((rs1.getString("name")));
 	    			System.out.println((rs1.getString("street_address")));
 	    			System.out.println((rs1.getString("phone")));
 	    			System.out.println((rs2.getString("name")));
 	    			System.out.println((rs2.getString("street_address")));
 	    			System.out.println((rs2.getString("phone")));
 	    			
 	    			
 	    			 writer.append(rs1.getString("idperson"));
	    		  writer.append(',');
	    		  writer.append(rs2.getString("idperson"));
	    		  
	    		
	    		  // writerData.append("Idperson1,Idperson2,Name,Street_address,City,State,Zipcode,Phone,Gender,Age,email,PII,Edcation,Occupation,Birthday");
	              
	    		  
	    		      writerData.append(rs1.getString("idperson"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("idperson"));
		    		  writerData.append(',');
		    		  writerData.append(rs1.getString("name"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("name"));
		    		  writerData.append(',');
		    		  writerData.append(nameWeight.toString());
		    		  writerData.append(',');
		    		  writerData.append(rs1.getString("state"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("state"));
		    		  writerData.append(',');
		    		  writerData.append(stateWeight.toString());
		    		  writerData.append(',');
		    		  writerData.append(rs1.getString("city"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("city"));
		    		  writerData.append(',');
		    		  writerData.append(cityWeight.toString());
		    		  writerData.append(',');
		    		  writerData.append(rs1.getString("street_address"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("street_address"));
		    		  writerData.append(',');
		    		  writerData.append(streetWeight.toString());
		    		  writerData.append(',');
		    		  writerData.append(rs1.getString("zipcode"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("zipcode"));
		    		  writerData.append(',');
		    		  writerData.append(zipWeight.toString());
		    		  writerData.append(',');
		    		  writerData.append(overallLocationWeight.toString());
		    		  writerData.append(',');
//		    		  writerData.append(stateWeight.toString());
//		    		  writerData.append(',');
//		    		  writerData.append(cityWeight.toString());
//		    		  writerData.append(',');
//		    		  writerData.append(streetWeight.toString());
//		    		  writerData.append(',');
//		    		  writerData.append(zipWeight.toString());
//		    		  writerData.append(',');
//		    		  writerData.append(overallLocationWeight.toString());
//		    		  writerData.append(',');
		    		  writerData.append(rs1.getString("phone"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("phone"));
		    		  writerData.append(','); 
		    		  writerData.append(phoneWeight.toString());
		    		  writerData.append(',');
		    		  writerData.append(rs1.getString("age"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("age"));
		    		  writerData.append(',');
		    		  writerData.append(ageWeight.toString());
		    		  writerData.append(',');
		    		  writerData.append(overallWeight.toString());
		    		  writerData.append(',');
		    		  writerData.append(rs1.getString("url"));
		    		  writerData.append(',');
		    		  writerData.append(rs2.getString("url")); 
		    		  writerData.append("\r\n");
	    		  
	    		 //System.out.println("rs2 name"+ rs2.getString("name"));
	    		  writer.append(',');
 	    		
 	    		  writer.append("Directed");
 	    		  writer.append(',');
 	    		  Integer id = rs1.getInt("idperson");
 	    		 writer.append(',');
 	    		 System.out.println(id);
 	    		  //writer.append(id.toString());
 	    		  //writer.append(',');
 	    		  
 	    		writer.append(rs1.getInt("idperson") + "|" + rs2.getInt("idperson"));
 	    		  
 	    		  writer.append(',');
 	    		  System.out.println(overallWeight);
 	    		  writer.append(overallWeight.toString());
 	    		  writer.append("\r\n");
 	    		 } //if loop ends
 	    		  
 	    		
 	    	   }
 		     
 	    
 	    	  }
 	    	 count++; 
 	    	   
 		   }
 	        
        }
    	catch(Exception e)
    	{
    	     e.printStackTrace();
    	}
 	    
 	    writer.flush();
	    writer.close();
	   writerData.flush();
	  writerData.close();
	  conn.close() ;
	}
	catch(IOException e)
	{
	     e.printStackTrace();
	} 
	
	catch(SQLException e)
	{
		e.printStackTrace();
	}
	
    }
   
   public static boolean containsOnlyNumbers(String str) {
	   
	   boolean isNumber = true;
	   try
	   
	   {
	   
	   /*
	   #
	   * To check if the number is valid integer number, use
	   #
	   * int parseInt(String str) method of
	   #
	   * Integer wrapper class.
	   #
	   *
	   #
	   * This method throws NumberFormatException if the
	   #
	   * argument string is not a valid integer number.
	   #
	   */
       Integer.parseInt(str);
	   
	  // System.out.println(str + " is valid integer number");
	   
	   }
	   
	   catch(NumberFormatException nme)
	   {
	   isNumber = false;
	  // System.out.println(str + " is not a valid integer number");
	  }
	   
	   return isNumber;
	   
}
   
   public static int findDups (String[] test){
	   // public static void main(String[] args) {
	   int z = 0;     
	   Set<String> s = new HashSet<String>();
	      // int numberOfDups; 
	        for (String a : test)
	            if (!s.add(a))
	             z=1;
	            	//System.out.println("Duplicate detected: " + a);
                 //  numberOfDups = a.size;
	            
	       // System.out.println(s.size() + " distinct words: " + s);
	    //}
	return s.size();
   }

  
   public static String[] join(String [] ... parms) {
	    // calculate size of target array
	    int size = 0;
	    for (String[] array : parms) {
	      size += array.length;
	    }

	    String[] result = new String[size];
	    
	    int j = 0;
	    for (String[] array : parms) {
	      for (String s : array) {
	        result[j++] = s;
	      }
	    }
	    return result;
	  }

   public static int getMinimumIdForName(String queryName)
   {
	   int minId = 0;
	   
	Connection  conn = getConn();
   	
   	try
   	{
   	Statement stmt = conn.createStatement();
		String query = "select min(idperson) from person where name like \'%" + queryName + "\';" ;
		   //executing query and get the minimum id
		System.out.println(query);
		   ResultSet rs1 = stmt.executeQuery(query);
		   if(rs1.next()){
              minId=rs1.getInt("min(idperson)");
           }
   	}
   	catch(SQLException se)
   	{
   		se.printStackTrace();
   	}
		   
	   
	   return minId;
	   
	   
	   
   }
   
   
   static Connection conn = null;
   static String userName = "root";
   static String password = "admin";
   static String url = "jdbc:mysql://localhost/aparna";
   
   public static Connection getConn()
   {
   	
   	try
   	{
   	Class.forName ("com.mysql.jdbc.Driver").newInstance ();
       conn = DriverManager.getConnection (url, userName, password);
       System.out.println ("Database connection established");
   	}
   	
   	catch(Exception e)
   	{
   		e.printStackTrace();
   	}
   	
   	return conn;
   	  	
   	}
   	
   
   public static void mergeRecords(int idperson1, int idperson2)
   {
	   
   }
   
   
   
   
   
}